﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using System.Reflection;

namespace SisVendas.DataAccess
{
   public class DataBaseHelper
    {
         #region Variáveis
        public static MySqlCommand _myCommand =  new MySqlCommand();
        private static MySqlTransaction _myTransaction; 
        #endregion


       #region Propriedades

        public MySqlConnection MyBdConnection { get; set; } 
        public string NomeStringConexao { get; set; }
     
        #endregion 
     
       #region Construtores 
    
        public DataBaseHelper() 
       {

           this.NomeStringConexao = ConfigurationManager.ConnectionStrings["conexao"].ConnectionString;

           if (string.IsNullOrEmpty(this.NomeStringConexao))
               throw new ApplicationException("");

           this.MyBdConnection = new MySqlConnection(this.NomeStringConexao);
       }

       #endregion 
    
       #region Métodos Privados 

        
       private string GetCorrectParameterName(string parameterName) 
       { 
           if (parameterName[0] != '@') 
           { 
               parameterName = "@" + parameterName; 
           } 
           return parameterName; 
       } 
    
       #endregion 
    
       #region Métodos Públicos 
    


       public static DataBaseHelper Create() 
       {
           return new DataBaseHelper(); 
       } 
    
    
       public void OpenConnection() 
       { 
           //se o objeto de transação for nulo, significa que nenhuma operação com transação esta sendo feita, 
           //neste caso, a conexão poderá ser aberta normalmente, do contrário, a conexão utilizada junto com o objeto
           //de tranção deverá ser utilizada, não sendo necessária a execução do código abaixo
           if (_myTransaction == null)
           {
               if (this.MyBdConnection.State == System.Data.ConnectionState.Closed)
               {
                   this.MyBdConnection.Open();
               }
           }
       } 
    
       public void CloseConection() 
       {
           //se o objeto de transação for nulo, significa que nenhuma operação com transação esta sendo feita, 
           //neste caso, a conexão poderá ser aberta normalmente, do contrário, a conexão utilizada junto com o objeto
           //de tranção deverá ser utilizada, não sendo necessária a execução do código abaixo
          if (_myTransaction == null)
           this.MyBdConnection.Close(); 
       } 
    
       public MySqlParameter BuildParameter(string nome, object valor, DbType tipo, int size) 
       { 
           MySqlParameter parametro = new MySqlParameter(this.GetCorrectParameterName(nome), valor); 
           parametro.DbType = tipo; 
           parametro.Size = size; 
           return parametro; 
       } 
    
       public void BuildParameter(string nome, object valor, DbType tipo, int size, List<MySqlParameter> listParametros) 
       { 
           MySqlParameter parametro = this.BuildParameter(nome, valor, tipo, size); 
           listParametros.Add(parametro); 
       } 
    
       public MySqlParameter BuildOutPutParameter(string nome, DbType tipo, int size) 
       { 
           MySqlParameter parametro = new MySqlParameter(); 
           parametro.ParameterName = this.GetCorrectParameterName(nome); 
           parametro.DbType = tipo; 
           parametro.Size = size; 
           parametro.Direction = ParameterDirection.Output; 
           return parametro; 
       }

       public void BuildOutPutParameter(string nome, DbType tipo, int size, List<MySqlParameter> listParametros) 
       { 
           MySqlParameter parametro = this.BuildOutPutParameter(nome, tipo, size); 
           listParametros.Add(parametro); 
       } 
    
       public void ExecuteNonQuery(MySqlCommand command) 
       { 
           command.ExecuteNonQuery(); 
       } 
    
     public void ExecuteNonQuery(MySqlCommand command, bool openConnection) 
     { 
         if (openConnection) 
         { 
             this.OpenConnection(); 
         } 
         this.ExecuteNonQuery(command); 
         if (openConnection) 
         { 
             this.CloseConection(); 
         }
     }


     public void ExecuteCommands(params MySqlCommand[] commands) 
     { 
         Exception erro = null; 
         MySqlTransaction trans = null; 
         try 
         { 
             this.MyBdConnection.Open(); 
             trans = this.MyBdConnection.BeginTransaction(); 
             for (int i = 0; i < commands.Length; i++) 
             { 
                 commands[i].Transaction = trans; 
                 this.ExecuteNonQuery(commands[i]); 
             } 
             trans.Commit(); 
             this.MyBdConnection.Close(); 
         } 
         catch(Exception ex) 
         { 
             trans.Rollback(); 
             erro = ex; 
         } 
         finally 
         { 
             this.MyBdConnection.Close(); 
         } 
  
         if (erro != null) 
         { 
             throw erro; 
         } 
     }

     public DataTable ExecuteQueryWithResult(string query, params MySqlParameter[] parameters)
     {
         Exception erro = null;
         DataTable dt = new DataTable();
         try
         {
             if (_myTransaction==null)
                this.OpenConnection();

             MySqlCommand command = this.MyBdConnection.CreateCommand();
             command.CommandText = query;

             if (_myTransaction != null)
                 command.Transaction = _myTransaction;
             
             if (parameters != null)
                command.Parameters.AddRange(parameters);
             
             MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);

             dataAdapter.Fill(dt);

             if (_myTransaction ==null)
                 this.CloseConection();
         }
         catch (Exception ex)
         {
             erro = ex;
         }
         finally
         {
             this.CloseConection();
         }

         if (erro != null)
         {
             throw erro;
         }
         return dt;

     }


     public List<T> ExecuteQueryWithResult<T>(string query, params MySqlParameter[] parameters) where T : new()
     {
         Exception erro = null;
         DataTable dt = new DataTable();
         try
         {
             if (_myTransaction == null)
                 this.OpenConnection();

             MySqlCommand command = this.MyBdConnection.CreateCommand();
             command.CommandText = query;

             if (_myTransaction != null)
                 command.Transaction = _myTransaction;

             if (parameters != null)
                 command.Parameters.AddRange(parameters);

             MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
             dataAdapter.Fill(dt);

             if (_myTransaction == null)
                 this.CloseConection();
         }
         catch (Exception ex)
         {
             erro = ex;
         }
         finally
         {
             this.CloseConection();
         }

         if (erro != null)
         {
             throw erro;
         }

         List<T> lista = GetList<T>(dt);
         return lista;

     }

    //Inicia a transação
     public void IniciarTransacao()
     {
         this.MyBdConnection.Open();
         _myTransaction = this.MyBdConnection.BeginTransaction();
     }

     public void CancelarTransacao()
     {
         
         if (_myTransaction != null)
         {
             _myTransaction.Rollback();
             _myTransaction = null;
             this.MyBdConnection.Close();
         }
     }

     public void FinalizarTransacao()
     {
         if (_myTransaction != null)
         {
             _myTransaction.Commit();
             _myTransaction = null;
             this.MyBdConnection.Close();
         }
     }

     public void ExecuteNonQuery(string query, params MySqlParameter[] parameters)
     {
         ExecuteNonQuery(query, CommandType.Text,parameters);
     }

    public void ExecuteNonQuery(string query, CommandType tipoComando,params MySqlParameter[] parameters)
    {
         Exception erro = null;
         try
         {       
             this.OpenConnection();
             
             MySqlCommand command = this.MyBdConnection.CreateCommand();
             command.CommandText = query;
             command.CommandType = tipoComando;

             //Configura o objeto command para utilizar a transaction caso a mesma tenha sido iniciada
             if (_myTransaction != null)
                 command.Transaction = _myTransaction;

             command.Parameters.AddRange(parameters);
             this.ExecuteNonQuery(command);

             this.CloseConection();
         }
         catch (Exception ex)
         {
             erro = ex;
         }
         finally
         {
             if (_myTransaction == null)
                 this.CloseConection();
         }

         if (erro != null)
         {
             throw erro;
         }
     }

    public object ExecuteScalar(string query, params MySqlParameter[] parameters)
    {
       return ExecuteScalar(query,CommandType.Text,parameters);
    }

    public object ExecuteScalar(string query, CommandType commandType, params MySqlParameter[] parameters)
    {
        Exception erro = null;
        Object objRetorno = -1;
        try
        {
            this.OpenConnection();

            MySqlCommand command = this.MyBdConnection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = query;

            //Configura o objeto para recuperar a transaction
            if (_myTransaction != null)
                command.Transaction = _myTransaction;

            command.Parameters.AddRange(parameters);

            objRetorno = command.ExecuteScalar();

            

    
            this.CloseConection();
        }
        catch (Exception ex)
        {
            erro = ex;
        }
        finally
        {
            if (_myTransaction == null)
                this.CloseConection();
        }

        if (erro != null)
        {
            throw erro;
        }

        return objRetorno;
    }


   

     public  List<T> GetList<T>(DataTable dt) where T : new()
    {
        List<T> lista = new List<T>();

        foreach (DataRow r in dt.Rows)
        {
            T t = new T();

            foreach (PropertyInfo pi in t.GetType().GetProperties())
            {
                if (dt.Columns.IndexOf(pi.Name) > -1)
                {
                    if (pi.PropertyType.Equals(typeof(Int32)))
                        pi.SetValue(t, int.Parse(r[pi.Name].ToString()), null);

                    if (pi.PropertyType.Equals(typeof(bool)))
                        pi.SetValue(t, bool.Parse(r[pi.Name].ToString()), null);

                    if (pi.PropertyType.Equals(typeof(string)))
                        pi.SetValue(t,r[pi.Name].ToString(), null);
                    
                }
            }


            lista.Add(t);
        }

        return lista;
    }

#endregion


     

    }
}

